﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace EP2_Gameserver
{
    public static class mySQL
    {

        public static string host, database, user, password;

        public static void Test()
        {
            string strProvider = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;
            MySqlConnection mysqlCon = new MySqlConnection(strProvider);
            try
            {
                mysqlCon.Open();
                LogScreen.Info("mySQL Connected");
                mysqlCon.Close();

            }
            catch
            {
                LogScreen.Error("mySQL Error");
            }
        }

        public static int VerifyLogin(string UserName, string Password)
        {
            string strProvider = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;
            MySqlConnection mysqlCon = new MySqlConnection(strProvider);
            mysqlCon.Open();

            MySqlCommand verifyUser = new MySqlCommand("SELECT a_index FROM t_users WHERE a_username=\"" + UserName + "\" AND a_password = \"" + Password + "\";", mysqlCon);
            verifyUser.ExecuteNonQuery();

            MySqlDataReader myReader = verifyUser.ExecuteReader();



            if (myReader.Read() == false) return 0;
            else
            {
                return Convert.ToInt32(myReader.GetValue(0).ToString());
            }

        }

        public static string GetCharIDs(int UserID)
        {
            
            string strProvider = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;
            MySqlConnection mysqlCon = new MySqlConnection(strProvider);
            mysqlCon.Open();

            MySqlCommand verifyUser = new MySqlCommand("SELECT a_index FROM t_characters WHERE a_user_index = "+ UserID.ToString() + ";", mysqlCon);
            verifyUser.ExecuteNonQuery();

            MySqlDataReader myReader = verifyUser.ExecuteReader();

            string ids = "";
            int CharCount = 0;

            while (myReader.Read())
            {
                for (int i = 0; i < myReader.FieldCount; i++)
                {
                    CharCount++;
                    if( CharCount == 1 ) ids += myReader.GetValue(i).ToString();
                    else ids += "-" + myReader.GetValue(i).ToString();
                }
            }

            return ids;

        }

        public static string[] PerformQuery(string query)
        {
            string strProvider = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;
            MySqlConnection mysqlCon = new MySqlConnection(strProvider);
            mysqlCon.Open();

            MySqlCommand verifyUser = new MySqlCommand(query, mysqlCon);
            verifyUser.ExecuteNonQuery();

            MySqlDataReader myReader = verifyUser.ExecuteReader();

            ArrayList list = new ArrayList();

            while (myReader.Read())
            {
                for (int i = 0; i < myReader.FieldCount; i++)
                {
                    
                    list.Add(myReader.GetValue(i).ToString());
                   
                }
            }

            return (string[])list.ToArray(typeof(string));

        }


        public static void CreateCharacter(int UserID, string CharName, byte CharClass, byte CharHair, byte CharFace)
        {
            

            string strProvider = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;
            MySqlConnection mysqlCon = new MySqlConnection(strProvider);
            mysqlCon.Open();

            string wearing = "";
            wearing += Config.newChar[CharClass][4].ToString() + " ";
            wearing += Config.newChar[CharClass][6].ToString() + " ";
            wearing += Config.newChar[CharClass][8].ToString() + " ";
            wearing += Config.newChar[CharClass][10].ToString() + " ";
            wearing += Config.newChar[CharClass][12].ToString() + " ";
            wearing += Config.newChar[CharClass][14].ToString() + " ";
            wearing += Config.newChar[CharClass][16].ToString() + " ";
            wearing += Config.newChar[CharClass][18].ToString();

            string plus = "";
            plus += Config.newChar[CharClass][5].ToString() + " ";
            plus += Config.newChar[CharClass][7].ToString() + " ";
            plus += Config.newChar[CharClass][9].ToString() + " ";
            plus += Config.newChar[CharClass][11].ToString() + " ";
            plus += Config.newChar[CharClass][13].ToString() + " ";
            plus += Config.newChar[CharClass][15].ToString() + " ";
            plus += Config.newChar[CharClass][17].ToString() + " ";
            plus += Config.newChar[CharClass][19].ToString();


            string query = "INSERT INTO t_characters (a_user_index, a_charname, a_class, a_face, a_hair, a_level, a_curhp, a_maxhp, a_curmp, a_maxmp, a_str, a_dex, a_int,a_con,a_curexp,a_maxexp,a_sp,a_wearing_id,a_wearing_plus) VALUES ";
            query += "(";
            query += UserID.ToString() + ",";
            query += "'" + CharName + "',";
            query += CharClass.ToString() + ",";
            query += CharFace.ToString() + ",";
            query += CharHair.ToString() + ",";
            query += Config.newChar[CharClass][3].ToString() + ",";
            query += Config.newChar[CharClass][1].ToString() + ",";
            query += Config.newChar[CharClass][1].ToString() + ",";
            query += Config.newChar[CharClass][2].ToString() + ",";
            query += Config.newChar[CharClass][2].ToString() + ",";
            query += Config.newChar[CharClass][20].ToString() + ",";
            query += Config.newChar[CharClass][21].ToString() + ",";
            query += Config.newChar[CharClass][22].ToString() + ",";
            query += Config.newChar[CharClass][23].ToString() + ",";
            query += 0.ToString() + ",";
            query += 100.ToString() + ",";
            query += 0.ToString() + ",";
            query += "'" + wearing + "',";  // wearing id
            query += "'" + plus + "'";  // wearing plus
            query += ");";
   

            MySqlCommand verifyUser = new MySqlCommand(query, mysqlCon);
            verifyUser.ExecuteNonQuery();


        }
        

    }
}
